package approval.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import models.util.MySqlDataAccessHelper;
import resource.pojo.BuyingDocument;


public class BuyingFormDAO {
//	
//	public static ArrayList<BuyingDocument> listBuyingForm() throws SQLException{
//		ArrayList<BuyingDocument> lst = new ArrayList<BuyingDocument>();
//		String sql = "select * from buying_document";
//		Connection conn = null;
//		PreparedStatement pstmt = null;
//		ResultSet rs = null;
//		try {
//			conn = getDBPoolConnection();
//			pstmt = conn.prepareStatement(sql);
//			rs = pstmt.executeQuery();
//			while (rs.next()) {
//				lst.add(new BuyingDocument(
//					rs.getString("id"),
//					rs.getInt("document_id"),
//					rs.getString("name"),
//					rs.getString("company"),
//					rs.getInt("amount"),
//					rs.getString("unit"),
//					rs.getFloat("price"),
//					rs.getFloat("vat"),
//					rs.getFloat("total")
//				));				
//			}
//		} catch (Exception ex){
//            ex.printStackTrace(System.out);
//        }finally {
//            if (pstmt != null) {
//            	DbUtils.close(pstmt);            	
//            }
//            if (conn != null) {
//            	DbUtils.close(conn);
//            }
//            if(rs != null){
//            	DbUtils.close(rs);
//            }
//        }
//		return lst;
//	}
//	
	public static String getTotalPriceItems(String database,String id){
		
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		String sql = "select SUM(db.total) as total from approvedoc ap,buying_document db where ap.IDApproveDoc='"+id+"' and db.document_id="+id+" Limit 1";
	
		ResultSet rs = null;
		String result="";
		try {
			mysql.open(database);
			rs = mysql.executeQuery(sql);
			while (rs.next()) {
				result=rs.getFloat("total")+"";
				//System.out.println();
			}
		} catch (Exception ex){
            ex.printStackTrace(System.out);
       }finally {
           mysql.close();
        }
		return result;
	}
	
//	
//	public static boolean insertBuyingForm(BuyingDocument buying) throws SQLException{
//		int row = 0;
//        String sql = "insert into buying_document (id,document_id,name,company,amount,price,vat,total) " +
//        			 "VALUES(?,?,?,?,?,?,?,?)";
//        Connection conn = null;
//        PreparedStatement pstmt = null;
//        ResultSet rs = null;
//        try {
//            conn = getDBPoolConnection();
//            pstmt = conn.prepareStatement(sql);
//            pstmt.setString(1, new Date().getTime()+"");
//            pstmt.setInt(2, buying.getDocument_id());
//            pstmt.setString(3, buying.getName());
//            pstmt.setString(4, buying.getCompany());
//            pstmt.setInt(5, buying.getAmount());
//            pstmt.setFloat(6, buying.getPrice());
//            pstmt.setFloat(7, buying.getVat());
//            pstmt.setFloat(8, buying.getTotal());            
//            row = pstmt.executeUpdate();                                  
//        } catch (Exception ex){
//            ex.printStackTrace(System.out);
//        }finally {
//            if (pstmt != null) {
//            	DbUtils.close(pstmt);            	
//            }
//            if (conn != null) {
//            	DbUtils.close(conn);
//            }
//            if(rs != null){
//            	DbUtils.close(rs);
//            }
//        }
//        return row > 0;
//	}
//
//	public static boolean updateBuyingForm(BuyingDocument buying) throws SQLException{
//		int row = 0;
//        String sql = "update buying_document set " +
//        		"name=?,company=?,amount=?,price=?,vat=?,total=? where id=?";
//        Connection conn = null;
//        PreparedStatement pstmt = null;
//        ResultSet rs = null;
//        try {
//            conn = getDBPoolConnection();
//            pstmt = conn.prepareStatement(sql);
//            pstmt.setString(1, buying.getName());
//            pstmt.setString(2, buying.getCompany());
//            pstmt.setInt(3, buying.getAmount());
//            pstmt.setFloat(4, buying.getPrice());
//            pstmt.setFloat(5, buying.getVat());
//            pstmt.setFloat(6, buying.getTotal());
//            pstmt.setString(7, buying.getId());
//            row = pstmt.executeUpdate();                                  
//        } catch (Exception ex){
//            ex.printStackTrace(System.out);
//        }finally {
//            if (pstmt != null) {
//            	DbUtils.close(pstmt);            	
//            }
//            if (conn != null) {
//            	DbUtils.close(conn);
//            }
//            if(rs != null){
//            	DbUtils.close(rs);
//            }
//        }
//        return row > 0;
//	}
//	
}
